﻿-- =============================================
-- Script Template
-- =============================================

--drop proc P_TrendChart_CJDLT_HMFB
CREATE procedure P_TrendChart_CJDLT_HMFB  --超级大乐透号码分布图

AS

set nocount on

Create table #LotteryNumberListCJDLT(id int identity(1,1),Isuse varchar(20),LotteryNumber varchar(100),
		B_1 int,B_2 int,B_3 int,B_4 int,B_5 int,B_6 int,B_7 int,B_8 int,B_9 int,B_10 int,
		B_11 int,B_12 int,B_13 int,B_14 int,B_15 int,B_16 int,B_17 int,B_18 int,B_19 int,B_20 int,
		B_21 int,B_22 int,B_23 int,B_24 int,B_25 int,B_26 int,B_27 int,B_28 int,B_29 int,B_30 int,
		B_31 int,B_32 int,B_33 int,B_34 int,B_35 int,H_Z int,D_X varchar(20),J_O varchar(20),W_H int,
		T_1 int,T_2 int,T_3 int,T_4 int,T_5 int,T_6 int,T_7 int,T_8 int,T_9 int,T_10 int,T_11 int,T_12 int)

declare @cur cursor 
declare  @Isuse varchar(20), @WinLotterNumber varchar(100), @id int 

declare @B_1 int,@B_2 int,@B_3 int,@B_4 int,@B_5 int,@B_6 int,@B_7 int,@B_8 int,@B_9 int,@B_10 int,
	@B_11 int,@B_12 int,@B_13 int,@B_14 int,@B_15 int,@B_16 int,@B_17 int,@B_18 int,@B_19 int,@B_20 int,
	@B_21 int,@B_22 int,@B_23 int,@B_24 int,@B_25 int,@B_26 int,@B_27 int,@B_28 int,@B_29 int,@B_30 int,
	@B_31 int,@B_32 int,@B_33 int,@B_34 int,@B_35 int,@T_1 int,@T_2 int,@T_3 int,@T_4 int,@T_5 int,@T_6 int,
	@T_7 int,@T_8 int,@T_9 int,@T_10 int,@T_11 int,@T_12 int,@J int, @O int,@D int,@X int

declare @W int,@Q int,@B int,@S int,@G int,@T_M_1 int, @T_M int, @H_Z int,@D_X varchar(20),@J_O varchar(20),@W_H int

DECLARE  @W_G int,@Q_G int,@B_G int,@S_G int,@G_G int

Create table #tb(Id int,PlayNo varchar(20),WinLotteryNumber varchar(100))


    insert into #tb select Id,PlayNo, WinLotteryNumber from pm_plays  with(nolock) where WinLotteryNumber <> '' and LotteryID =39 order by Cast(PlayNo as int) desc


set @B_1 = 0 set @B_2 = 0 set @B_3 = 0 set @B_4 = 0 set @B_5 = 0 set @B_6 = 0  set @B_7 = 0 set @B_8 = 0 set @B_9 = 0
set @B_10 = 0 set @B_11 = 0 set @B_12 = 0 set @B_13 = 0 set @B_14 = 0 set @B_15 = 0 set @B_16 = 0  set @B_17 = 0 set @B_18 = 0 
set @B_19 = 0 set @B_20 = 0 set @B_21 = 0 set @B_22 = 0 set @B_23 = 0 set @B_24 = 0 set @B_25 = 0 set @B_26 = 0  set @B_27 = 0 set @B_28 = 0 
set @B_29 = 0 set @B_30 = 0 set @B_31 = 0 set @B_32 = 0 set @B_33 = 0 set @B_34 = 0 set @B_35 = 0

set @T_1 = 0 set @T_2 = 0 set @T_3 = 0 set @T_4 = 0 set @T_5 = 0 set @T_6 = 0  set @T_7 = 0 set @T_8 = 0 set @T_9 = 0
set @T_10 = 0 set @T_11 = 0 set @T_12 = 0 

set @cur = cursor scroll for select PlayNo,WinLotteryNumber from #tb order by Cast(PlayNo as int) asc

open @cur

fetch first from @cur into @Isuse, @WinLotterNumber

while @@fetch_status=0
begin

set @W = Substring(@WinLotterNumber,1,2) 
set @Q = Substring(@WinLotterNumber,4,2) 
set @B = Substring(@WinLotterNumber,7,2)
set @S = Substring(@WinLotterNumber,10,2) 
set @G = Substring(@WinLotterNumber,13,2)

set @W_G = Substring(@WinLotterNumber,2,1) 
set @Q_G = Substring(@WinLotterNumber,5,1) 
set @B_G = Substring(@WinLotterNumber,8,1)
set @S_G = Substring(@WinLotterNumber,11,1) 
set @G_G = Substring(@WinLotterNumber,14,1)

set @T_M_1 = Substring(@WinLotterNumber,18,2) 
set @T_M = Substring(@WinLotterNumber,21,2)

set @J = 0 SET @O = 0 SET @D = 0 SET @X = 0
		
set @B_1 =@B_1 + 1 set @B_2 = @B_2 + 1 set @B_3 = @B_3 + 1 set @B_4 = @B_4 + 1 
set @B_5 = @B_5 + 1 set @B_6 = @B_6 + 1  set @B_7 = @B_7 + 1 set @B_8 = @B_8 + 1 set @B_9 = @B_9 + 1 
set @B_10 = @B_10 + 1 set @B_11 =@B_11 + 1 set @B_12 = @B_12 + 1 set @B_13 = @B_13 + 1 set @B_14 = @B_14 + 1 
set @B_15 = @B_15 + 1 set @B_16 = @B_16 + 1  set @B_17 = @B_17 + 1 set @B_18 = @B_18 + 1 set @B_19 = @B_19 + 1 
set @B_20 = @B_20 + 1 set @B_21 =@B_21 + 1 set @B_22 = @B_22 + 1 set @B_23 = @B_23 + 1 set @B_24 = @B_24 + 1 
set @B_25 = @B_25 + 1 set @B_26 = @B_26 + 1  set @B_27 = @B_27 + 1 set @B_28 = @B_28 + 1 set @B_29 = @B_29 + 1 
set @B_30 = @B_30 + 1 set @B_31 =@B_31 + 1 set @B_32 = @B_32 + 1 set @B_33 = @B_33 + 1 set @B_34 = @B_34 + 1 
set @B_35 = @B_35 + 1 set @T_1 = @T_1 + 1  set @T_2 = @T_2 + 1  set @T_3 = @T_3 + 1 set @T_4 = @T_4 + 1
set @T_5 = @T_5 + 1 set @T_6 = @T_6 + 1 set @T_7 = @T_7 + 1 set @T_8 = @T_8 + 1 set @T_9 = @T_9 + 1 set @T_10 = @T_10 + 1
set @T_11 = @T_11 + 1 set @T_12 = @T_12 + 1

IF(@W = 1 OR @Q=1 OR @B=1 OR @S=1 OR @G=1)
BEGIN
	SET @B_1 = 0
END

IF(@W = 02 OR @Q=2 OR @B=2 OR @S=2 OR @G=2)
BEGIN
	SET @B_2 = 0
END

IF(@W = 3 OR @Q=03 OR @B=3 OR @S=3 OR @G=3)
BEGIN
	SET @B_3 = 0
END

IF(@W = 4 OR @Q=4 OR @B=4 OR @S=4 OR @G=4)
BEGIN
	SET @B_4 = 0
END

IF(@W = 5 OR @Q=5 OR @B=5 OR @S=5 OR @G=5)
BEGIN
	SET @B_5 = 0
END

IF(@W = 6 OR @Q=6 OR @B=6 OR @S=6 OR @G=6)
BEGIN
	SET @B_6 = 0
END

IF(@W = 7 OR @Q=7 OR @B=7 OR @S=7 OR @G=7)
BEGIN
	SET @B_7 = 0
END

IF(@W = 8 OR @Q=8 OR @B=8 OR @S=8 OR @G=8)
BEGIN
	SET @B_8 = 0
END

IF(@W = 9 OR @Q=9 OR @B=9 OR @S=9 OR @G=9)
BEGIN
	SET @B_9 = 0
END

IF(@W = 10 OR @Q=10 OR @B=10 OR @S=10 OR @G=10)
BEGIN
	SET @B_10 = 0
END

IF(@W = 11 OR @Q=11 OR @B=11 OR @S=11 OR @G=11)
BEGIN
	SET @B_11 = 0
END

IF(@W = 12 OR @Q=12 OR @B=12 OR @S=12 OR @G=12)
BEGIN
	SET @B_12 = 0
END

IF(@W = 13 OR @Q=13 OR @B=13 OR @S=13 OR @G=13)
BEGIN
	SET @B_13 = 0
END

IF(@W = 14 OR @Q=14 OR @B=14 OR @S=14 OR @G=14)
BEGIN
	SET @B_14 = 0
END

IF(@W = 15 OR @Q=15 OR @B=15 OR @S=15 OR @G=15)
BEGIN
	SET @B_15 = 0
END

IF(@W = 16 OR @Q=16 OR @B=16 OR @S=16 OR @G=16)
BEGIN
	SET @B_16 = 0
END

IF(@W = 17 OR @Q=17 OR @B=17 OR @S=17 OR @G=17)
BEGIN
	set @B_17 = 0
END

IF(@W = 18 OR @Q=18 OR @B=18 OR @S=18 OR @G=18)
BEGIN
	SET @B_18 = 0
END

IF(@W = 19 OR @Q=19 OR @B=19 OR @S=19 OR @G=19)
BEGIN
	SET @B_19 = 0
END

IF(@W = 20 OR @Q=20 OR @B=20 OR @S=20 OR @G=20)

BEGIN

	SET @B_20 = 0
END

IF(@W = 21 OR @Q=21 OR @B=21 OR @S=21 OR @G=21)
BEGIN
	SET @B_21 = 0
END

IF(@W = 22 OR @Q=22 OR @B=22 OR @S=22 OR @G=22)
BEGIN
	SET @B_22 = 0
END

IF(@W = 23 OR @Q=23 OR @B=23 OR @S=23 OR @G=23)
BEGIN
	SET @B_23 = 0
END

IF(@W = 24 OR @Q=24 OR @B=24 OR @S=24 OR @G=24)
BEGIN
	SET @B_24 = 0
END

IF(@W = 25 OR @Q=25 OR @B=25 OR @S=25 OR @G=25)
BEGIN
	SET @B_25 = 0
END

IF(@W = 26 OR @Q=26 OR @B=26 OR @S=26 OR @G=26)
BEGIN
	SET @B_26 = 0
END

IF(@W = 27 OR @Q=27 OR @B=27 OR @S=27 OR @G=27)
BEGIN
	set @B_27 = 0
END

IF(@W = 28 OR @Q=28 OR @B=28 OR @S=28 OR @G=28)
BEGIN

	SET @B_28 = 0
END

IF(@W = 29 OR @Q=29 OR @B=29 OR @S=29 OR @G=29)
BEGIN
	SET @B_29 = 0
END

IF(@W = 30 OR @Q=30 OR @B=30 OR @S=30 OR @G=30)
BEGIN
	SET @B_30 = 0
END

IF(@W = 31 OR @Q=31 OR @B=31 OR @S=31 OR @G=31)
BEGIN
	SET @B_31 = 0
END

IF(@W = 32 OR @Q=32 OR @B=32 OR @S=32 OR @G=32)
BEGIN
	SET @B_32 = 0
END

IF(@W = 33 OR @Q=33 OR @B=33 OR @S=33 OR @G=33)
BEGIN
	SET @B_33 = 0
END

IF(@W = 34 OR @Q=34 OR @B=34 OR @S=34 OR @G=34)
BEGIN
	SET @B_34 = 0
END

IF(@W = 35 OR @Q=35 OR @B=35 OR @S=35 OR @G=35)
BEGIN
	SET @B_35 = 0
END

IF(@T_M_1 = 1 OR @T_M = 1)
BEGIN
	SET @T_1 = 0
END

IF(@T_M_1 = 2 OR @T_M = 2)
BEGIN
	SET @T_2 = 0
END

IF(@T_M_1 = 3 OR @T_M = 3)
BEGIN
	SET @T_3 = 0
END

IF(@T_M_1 = 4 OR @T_M = 4)
BEGIN
	SET @T_4 = 0
END

IF(@T_M_1 = 5 OR @T_M = 5)
BEGIN
	SET @T_5 = 0
END

IF(@T_M_1 = 6 OR @T_M = 6)
BEGIN
	SET @T_6 = 0
END

IF(@T_M_1 = 7 OR @T_M = 7)
BEGIN
	SET @T_7 = 0
END

IF(@T_M_1 = 8 OR @T_M = 8)
BEGIN
	SET @T_8 = 0
END

IF(@T_M_1 = 9 OR @T_M = 9)
BEGIN
	SET @T_9 = 0
END

IF(@T_M_1 = 10 OR @T_M = 10)
BEGIN
	SET @T_10 = 0
END

IF(@T_M_1 = 11 OR @T_M = 11)
BEGIN
	SET @T_11 = 0
END

IF(@T_M_1 = 12 OR @T_M = 12)
BEGIN
	SET @T_12 = 0
END

SET @H_Z = @W + @Q + @B +@S + @G

set @W_H = @W_G +@Q_G +@B_G + @S_G +@G_G

if(@W > 17)
BEGIN
	SET @D=@D + 1
END

if(@Q > 17)
BEGIN 
	SET @D=@D + 1
END

if(@B > 17)
BEGIN   
	SET @D=@D + 1
END

if(@S > 17)
BEGIN   
	SET @D=@D + 1
END

if(@G > 17)
BEGIN   
	SET @D=@D + 1
END

IF(@W < 18)
BEGIN
	SET @X = @X + 1
END

IF(@Q < 18)
BEGIN
	SET @X = @X + 1
END

IF(@B < 18)
BEGIN
	SET @X = @X + 1
END

IF(@S < 18)
BEGIN
	SET @X = @X + 1
END

IF(@G < 18)
BEGIN
	SET @X = @X + 1
END

SET @D_X =(rtrim(ltrim(str(@D))) +':'+rtrim(ltrim(str(@X))))

IF(@W % 2 = 1)
BEGIN
	SET @J=@J+1
END

IF(@Q % 2 = 1)
BEGIN
	SET @J=@J+1
END

IF(@B % 2 = 1)
BEGIN
	SET @J=@J+1
END

IF(@S % 2 = 1)
BEGIN
	SET @J=@J+1
END

IF(@G % 2 = 1)
BEGIN
	SET @J=@J+1
END

IF(@W % 2= 0)
BEGIN
	SET @O = @O + 1
END

IF(@Q % 2= 0)
BEGIN
	SET @O = @O + 1
END

IF(@B %2 = 0)
BEGIN
	SET @O = @O + 1
END

IF(@S % 2= 0)
BEGIN
	SET @O = @O + 1
END

IF(@G % 2= 0)
BEGIN
	SET @O = @O + 1
END

SET @J_O = (rtrim(ltrim(str(@J))) +':'+rtrim(ltrim(str(@O))))

insert into #LotteryNumberListCJDLT values (@Isuse, @WinLotterNumber ,@B_1,@B_2 ,@B_3 ,@B_4 ,@B_5,@B_6 ,@B_7 ,@B_8,@B_9,@B_10,
		@B_11,@B_12 ,@B_13 ,@B_14 ,@B_15,@B_16 ,@B_17 ,@B_18,@B_19,@B_20,@B_21,@B_22 ,@B_23,			
		@B_24 ,@B_25,@B_26 ,@B_27 ,@B_28,@B_29,@B_30,@B_31,@B_32 ,@B_33,@B_34 ,@B_35,@H_Z,
		@D_X,@J_O,@W_H,@T_1,@T_2,@T_3,@T_4,@T_5,@T_6,@T_7,@T_8,@T_9,@T_10,@T_11,@T_12)

fetch next from @cur into @Isuse, @WinLotterNumber

end

close @cur

select * from #LotteryNumberListCJDLT

GO
exec P_TrendChart_CJDLT_HMFB


